
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using Excel;
using Newtonsoft.Json;
using OfficeOpenXml;

using UnityEngine;



public class ExcelExchangeTools
{
    
    public void WriteToExcel(string path)
    {
        // string path = "Assets/Resources/数据配置/Data关卡详情 1.xlsx";
        FileInfo xlsxFile = new FileInfo(path);
 
        if (xlsxFile.Exists)
        {
            //通过ExcelPackage打开文件
            using (ExcelPackage package = new ExcelPackage(xlsxFile))
            {
                //修改excel的第一个sheet，下标从1开始
                
 
                for (int i = 0; i < 3; i++)
                {
                    //修复selectedIndex + 2行，i + 1列数据，下标从1开始，第一行因为是描述信息所以是selectedIndex + 2
                    worksheet.Cells[1, i + 1].Value = "mmmm";
                }
 
                package.Save();
                Debug.Log("WriteToExcel Success");
            }
        }
    }

    private ExcelPackage package=null;
    private ExcelWorksheet worksheet;

    private void StartWrite(string path)
    {
        if (package != null)
        {
            Debug.Log("正在修改中");
            package.Dispose();
            package = null;
            return;
        }

        FileInfo xlsxFile = new FileInfo(path);
        package = new ExcelPackage(xlsxFile);
        worksheet = package.Workbook.Worksheets[1];
    }

    public void AddAllRow(List<object> os)
    {
        Clear();
        if (os.Count == 0) return;

        var t = os[0].GetType();
        var allfield = t.GetFields();
        
        int rowmin = 4;
        for (int i = 0; i < os.Count; i++)
        {
            int row = rowmin + i;
            foreach (var VARIABLE in allfield)
            {
                int index = GetColIndex(VARIABLE.Name);
                // worksheet.Cells[row, index + 1].Start;
                var v = VARIABLE.GetValue(os[i]);
                worksheet.Cells[row, index + 1].Value = v;
            }
        }
    }
    
    public void AddRow(object o)
    {
        var t = o.GetType();
        var allfield = t.GetFields();
        int row = mSheet.Rows.Count + 1;
        foreach (var VARIABLE in allfield)
        {
            try
            {
                int index = GetColIndex(VARIABLE.Name);
                // worksheet.Cells[row, index + 1].Start;
                var v = VARIABLE.GetValue(o);
                worksheet.Cells[row, index + 1].Value = v;
            }
            catch (Exception e)
            {
                Debug.LogError(e);
            }
        }
        
    }

    public void Clear()
    {
        if (mSheet.Rows.Count > 3)
        {
            for (int i = 4; i <= mSheet.Rows.Count; i++)
            {
                for (int j = 1; j <= mSheet.Columns.Count; j++)
                {
                    worksheet.Cells[i,j].Clear();
                }
            }
        }
    }

    public void SetValue(int id, string key, string value)
    {
        if (package == null)
        {
            Debug.Log("没有开始");
            return;
        }
        
        int rowindex = GetRowIndex(id);
        int colindex = GetColIndex(key);
        if (rowindex == -1)
        {
            Debug.LogError($"没找到{id}_{key}_{value}");
            return;
        }
        if (colindex == -1)
        {
            Debug.LogError($"没找到{id}_{key}_{value}");
            return;
        }

        
        // string s成员 = Base逆推String.Create(value.GetType(), value).GetValue();
        
        worksheet.Cells[rowindex + 1,colindex + 1].Value = value;
    }

    public void SaveExcel()
    {
        if (package == null)
        {
            Debug.Log("没有开始");
            return;
        }
        package.Save();
        package.Dispose();
        package = null;
        worksheet = null;
    }

    private DataTable mSheet = null;

    public int GetRowIndex(int Id)
    {
        for (int i = 1; i < mSheet.Rows.Count; i++)
        {
            int index;
            
            if (int.TryParse(mSheet.Rows[i][0].ToString(),out index) &&  index== Id)
            {
                return i;
            }
        }
        return -1;
    }
    public int GetColIndex(string key)
    {
        for (int i = 0; i < mSheet.Columns.Count; i++)
        {
            if (mSheet.Rows[2][i].ToString() == key)
            {
                return i;
            }
        }
        return -1;
    }

    public bool InitSuccess;

    public ExcelExchangeTools(string excelFile,OpType type)
    {
        InitSuccess = false;
        FileStream mStream = File.Open(excelFile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
        IExcelDataReader mExcelReader = ExcelReaderFactory.CreateOpenXmlReader(mStream);
        var mResultSet = mExcelReader.AsDataSet();
        if (mResultSet.Tables.Count > 0)
        {
            //默认读取第一个数据表
            mSheet = mResultSet.Tables[0];
        }

        if (type == OpType.ReadAndWrite)
        {
            StartWrite(excelFile);
        }

        InitSuccess = true;
    }
    
    public enum OpType
    {
        OnlyRead,
        ReadAndWrite
    }

    public void Test()
    {
        for (int i = 1; i < mSheet.Rows.Count; i++)
        {
            mSheet.Rows[i][0] = "111";
        }
        
    }

    /// <summary>
    /// 获取json
    /// </summary>
    /// <param name="tableName"></param>
    /// <param name="IdX"></param>
    /// <param name="IdY"></param>
    /// <param name="keepFieldList"></param>
    /// <returns>返回json 和是否失败</returns>
    public (string, bool) GetJson(ref int IdX, ref int IdY)
    {
        IdX = -1;
        IdY = -1;
        //判断Excel文件中是否存在数据表
        if (mSheet == null)
        {
            return ("", false);
        }

        //默认读取第一个数据表

        //判断数据表内是否存在数据
        if (mSheet.Rows.Count < 1)
        {
            return ("", false);
        }

        //准备一个列表存储整个表的数据
        List<Dictionary<string, object>> table = new List<Dictionary<string, object>>();
        /************Keep * Mode 保留带*的行列 ********************/
        /*   server |      |   *   |
         *   local  |  *   |   *   |
         *          | Id   |   xxx |
         *     *    | 1    |       |
         *     *    | 2    |       |
         *
         * 带*的行列保留
         */
        List<object> fieldNameRowDatas = new List<object>();
        //
        List<object> fieldTypeRowDatas = new List<object>();
        //第一行为备注，
        //寻找到id字段行数，以下全为数据
        int skipRowCount = -1;
        int skipColCount = -1;

        //这里skip 防止有人在 备注行直接输入id
        int skipLine = 1;

        for (int i = skipLine; i < 10 && skipColCount == -1; i++)
        {
            var rows = this.GetRowDatas(i);
            //遍历rows
            for (int j = 0; j < rows.Count; j++)
            {
                if (rows[j].Equals("Id"))
                {
                    skipRowCount = i;
                    skipColCount = j;
                    fieldNameRowDatas = rows;
                    //获取字段类型
                    var rowtype = this.GetRowDatas(i - 1);
                    fieldTypeRowDatas = rowtype;
                    //
                    break;
                }
            }
        }

        if (skipRowCount == -1)
        {
            Debug.LogError("表格数据可能有错,没发现Id字段,请检查");
            return ("{}", false);
        }


        IdX = skipColCount;
        IdY = skipRowCount;

        bool isSuccess = true;
        //读取数据
        for (int i = skipRowCount + 1; i < mSheet.Rows.Count; i++)
        {
            //准备一个字典存储每一行的数据
            Dictionary<string, object> row = new Dictionary<string, object>();

            for (int j = skipColCount; j < mSheet.Columns.Count; j++)
            {
                string field = fieldNameRowDatas[j].ToString();
                //跳过空字段
                if (string.IsNullOrEmpty(field))
                {
                    continue;
                }

                //Key-Value对应
                var rowdata = mSheet.Rows[i][j];
                // mSheet.Rows[i][j] = "aaa";
                //根据null判断
                if (rowdata == null)
                {
                    Debug.LogErrorFormat("表格数据为空：[{0},{1}]", i, j);
                    continue;
                }

                var fieldType = fieldTypeRowDatas[j].ToString().ToLower();
                if (rowdata is DBNull) //空类型判断，赋默认值
                {
                    if (fieldType == "int" || fieldType == "float" || fieldType == "double")
                    {
                        row[field] = 0;
                    }
                    else if (fieldType.Equals("string", StringComparison.OrdinalIgnoreCase))
                    {
                        row[field] = "";
                    }
                    else if (fieldType.Equals("bool", StringComparison.OrdinalIgnoreCase))
                    {
                        row[field] = false;
                    }
                    // else if (fieldType.Contains("[]") || fieldType.Contains("list<")) //空数组
                    // {
                    //     if (fieldType.Equals("int[]", StringComparison.OrdinalIgnoreCase) ||
                    //         fieldType.Equals("list<int>", StringComparison.OrdinalIgnoreCase))
                    //     {
                    //         row[field] = new int[0];
                    //     }
                    //     else if (fieldType.Equals("float[]", StringComparison.OrdinalIgnoreCase) ||
                    //              fieldType.Equals("list<float>", StringComparison.OrdinalIgnoreCase))
                    //     {
                    //         row[field] = new float[0];
                    //     }
                    //     else if (fieldType.Equals("double[]", StringComparison.OrdinalIgnoreCase) ||
                    //              fieldType.Equals("list<double>", StringComparison.OrdinalIgnoreCase))
                    //     {
                    //         row[field] = new double[0];
                    //     }
                    //     else if (fieldType.Equals("string[]", StringComparison.OrdinalIgnoreCase) ||
                    //              fieldType.Equals("list<string>", StringComparison.OrdinalIgnoreCase))
                    //     {
                    //         row[field] = new string[0];
                    //     }
                    //     else if (fieldType.Equals("bool[]", StringComparison.OrdinalIgnoreCase) ||
                    //              fieldType.Equals("list<bool>", StringComparison.OrdinalIgnoreCase))
                    //     {
                    //         row[field] = new bool[0];
                    //     }
                    // }
                }
                else
                {
                    // //string数组，对单个元素加上""
                    // if (fieldType.Contains("[]") || fieldType.Contains("list<")) //空数组
                    // {
                    //     var value = rowdata.ToString();
                    //     if (fieldType.Equals("string[]") ||
                    //         fieldType.Equals("list<string>", StringComparison.OrdinalIgnoreCase))
                    //     {
                    //         string[] strArray = null;
                    //         //空数组
                    //         if (value == "[]" || value == "\"[]\"")
                    //         {
                    //             strArray = new string[0];
                    //         }
                    //         else
                    //         {
                    //             //非空数组
                    //             value = value.Replace("\"[", "").Replace("]\"", "");
                    //             value = value.Replace("[", "").Replace("]", "");
                    //             var strs = value.Split(',');
                    //             strArray = new string[strs.Length];
                    //             for (int k = 0; k < strs.Length; k++)
                    //             {
                    //                 var str = strs[k];
                    //                 //移出\"\"
                    //                 if (str.StartsWith("\""))
                    //                 {
                    //                     str = str.Remove(0, 1);
                    //                     str = str.Remove(str.Length - 1, 1);
                    //                 }
                    //
                    //                 strArray[k] = str;
                    //             }
                    //         }
                    //
                    //
                    //
                    //         //解析
                    //         row[field] = strArray;
                    //     }
                    //     else
                    //     {
                    //         value = value.Replace("\"[", "[").Replace("]\"", "]");
                    //         if (fieldType.Equals("int[]", StringComparison.OrdinalIgnoreCase) ||
                    //             fieldType.Equals("list<int>", StringComparison.OrdinalIgnoreCase))
                    //         {
                    //             var ret = JsonConvert.DeserializeObject<int[]>(value);
                    //             // var ret = JsonMapper.ToObject<int[]>(value);
                    //             row[field] = ret;
                    //         }
                    //         else if (fieldType.Equals("float[]", StringComparison.OrdinalIgnoreCase) ||
                    //                  fieldType.Equals("list<float>", StringComparison.OrdinalIgnoreCase))
                    //         {
                    //             var ret = JsonMapper.ToObject<float[]>(value);
                    //             row[field] = ret;
                    //         }
                    //         else if (fieldType.Equals("double[]", StringComparison.OrdinalIgnoreCase) ||
                    //                  fieldType.Equals("list<double>", StringComparison.OrdinalIgnoreCase))
                    //         {
                    //             var ret = JsonMapper.ToObject<double[]>(value);
                    //             row[field] = ret;
                    //         }
                    //         else if (fieldType.Equals("bool[]", StringComparison.OrdinalIgnoreCase) ||
                    //                  fieldType.Equals("list<bool>", StringComparison.OrdinalIgnoreCase))
                    //         {
                    //             var ret = JsonMapper.ToObject<bool[]>(value);
                    //             row[field] = ret;
                    //         }
                    //     }
                    // }
                    // //数值
                    // else 
                    if (fieldType == "int" || fieldType == "float" || fieldType == "double")
                    {
                        var oldValue = rowdata.ToString();
                        if (fieldType == "int")
                        {
                            int value = 0;
                            if (int.TryParse(oldValue, out value))
                            {
                                row[field] = value;
                            }
                            else
                            {
                                row[field] = 0;
                                Debug.LogError(
                                    $"表格数据类型出错:{i}-{j}, 字段名:{fieldNameRowDatas[j]}！要求类型:{fieldTypeRowDatas[j]}，实际类型：{oldValue.GetType()} - value:{oldValue} ");
                                isSuccess = false;
                            }
                        }
                        else if (fieldType == "float")
                        {
                            float value = 0;
                            if (float.TryParse(oldValue, out value))
                            {
                                row[field] = value;
                            }
                            else
                            {
                                row[field] = 0;
                                Debug.LogError(
                                    $"表格数据类型出错:{i}-{j}, 字段名:{fieldNameRowDatas[j]}！要求类型:{fieldTypeRowDatas[j]}，实际类型：{oldValue.GetType()} - value:{oldValue} ");
                                isSuccess = false;
                            }
                        }
                        else if (fieldType == "double")
                        {
                            double value = 0;
                            if (double.TryParse(oldValue, out value))
                            {
                                row[field] = value;
                            }
                            else
                            {
                                row[field] = 0;
                                Debug.LogError(
                                    $"表格数据类型出错:{i}-{j}, 字段名:{fieldNameRowDatas[j]}！要求类型:{fieldTypeRowDatas[j]}，实际类型：{oldValue.GetType()} - value:{oldValue} ");
                                isSuccess = false;
                            }
                        }
                    }
                    else if (fieldType.Equals("string"))
                    {
                        var str = rowdata.ToString();
                        if (str == "\"\"")
                        {
                            row[field] = "";
                        }
                        else
                        {
                            row[field] = str;
                        }
                    }
                    else
                    {
                        row[field] = rowdata;
                    }
                }
            }

            //添加到表数据中
            if (row.Count > 0)
            {
                table.Add(row);
            }
        }

        //生成Json字符串
        // string json = JsonMapper.ToJson(table);
        string json = JsonConvert.SerializeObject(table);

        return (json, isSuccess);
    }


    /// <summary>
    /// 获取一行数据
    /// </summary>
    /// <param name="index"></param>
    /// <returns></returns>
    public List<object> GetRowDatas(int index)
    {
        List<object> list = new List<object>();

        //判断Excel文件中是否存在数据表
        if (mSheet == null)
        {
            return list;
        }

        //默认读取第一个数据表

        //判断数据表内是否存在数据
        if (mSheet.Rows.Count <= index)
        {
            return list;
        }

        //读取数据
        int colCount = mSheet.Columns.Count;
        for (int j = 0; j < colCount; j++)
        {
            object item = mSheet.Rows[index][j];
            list.Add(item);
        }


        return list;
    }
}
